<?php

namespace App\Exports;

use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;

use App\Services\Designer\Model\DesignerDemand;

class CourseRecordExport implements FromCollection, WithEvents , WithStrictNullComparison
{
    use Exportable;

    protected $id;
    protected $count;

    public function __construct(int $id)
    {
        $this->id = $id;
    }

    public function collection()
    {
        // 这里使用 collect 方法生成 collection，通常模型取出的数据即为 collection 格式
        $collection = new Collection($this->createData());
        return $collection;
    }

    protected function createData()
    {
        $course = DB::table('kd_course AS course')
            ->where('course.id', $this->id)
            ->select([
                'course.title AS title'
            ])
            ->first();
        $heading = [
            [$course->title.'学习记录','',''],
            ['数据导出日期：'.date('Y年m月d日'),'',''],
            ['姓名','学习时间','考试成绩'],
        ];
        $data = $this->queryData($this->id);
        $this->count = count($data);
        return array_merge($heading, $data);
    }

    protected function queryData($id)
    {
        $ym = date('Y').date('m');
        $seconds = DB::table('kd_course AS course')
            ->leftJoin('kd_course_record AS cr', 'course.id', 'cr.course_id')
            ->leftJoin('users AS u', 'u.uid', 'cr.user_id')
            ->where('course.id', $id)
            ->where('cr.year_month', $ym)
            ->select([
                'cr.user_id AS user_id',
                'u.name AS name',
                'course.title AS title',
                DB::raw('SUM(cr.second_count) AS second_count')
            ])
            ->groupBy('cr.user_id')
            ->orderBy('second_count','DESC')
            ->get();

        $grades = DB::table('kd_course AS course')
            ->leftJoin('kd_course_paper_record AS cpr', 'course.id', 'cpr.course_id')
            ->leftJoin('users AS u', 'u.uid', 'cpr.user_id')
            ->where('course.id', $id)
            ->where('cpr.year_month', $ym)
            ->select([
                'cpr.user_id AS user_id',
                'u.name AS name',
                'cpr.grade AS grade'
            ])
            ->groupBy('cpr.user_id')
            ->orderBy('cpr.grade','DESC')
            ->get();
        $rows = array();
        foreach($seconds as $second){
            $row = array('name' => $second->name, 'second_count' => round($second->second_count/60, 1).'分钟', 'grade' => 0);
            $rows[$second->user_id] = $row;
        }
        foreach($grades as $grade){
            if(!empty($rows[$grade->user_id])){
                $rows[$grade->user_id]['grade'] = $grade->grade;
            }else{
                $row = array('name' => $grade->name, 'second_count' => 0, 'grade' => $grade->grade);
                $rows[$grade->user_id] = $row;
            }
        }
        $datas = array();
        foreach($rows as $row){
            $datas[] = $row;
        }


        $res = [];
        foreach ($datas as $k=>$datum)
        {
            $res[$k][]= $datum['name'];
            $res[$k][]= $datum['second_count'];
            $res[$k][]= $datum['grade'];
        }

        return $res;
    }

    /**
     * 注册事件
     * @return array
     */
    public function registerEvents(): array
    {
        return [
            AfterSheet::class  => function(AfterSheet $event) {
                //设置列宽
                $event->sheet->getDelegate()->getColumnDimension('A')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('B')->setWidth(20);
                $event->sheet->getDelegate()->getColumnDimension('C')->setWidth(20);
                //设置区域单元格字体、颜色、背景等，其他设置请查看 applyFromArray 方法，提供了注释
                $event->sheet->getDelegate()->getStyle('A1:C1')->applyFromArray([
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                        'wrapText' => true,
                    ],
                    'font' => [
                        'name' => '等线',
                        'size' => '22',
                        'bold' => true,
                        'italic' => false,
                        'strikethrough' => false,
                    ],
                ]);
                $event->sheet->getDelegate()->getStyle('A2:C2')->applyFromArray([
                    'font' => [
                        'name' => 'Arial',
                        'bold' => true,
                        'italic' => false,
                        'strikethrough' => false,
                        'color' => [
                            'rgb' => 'FF0000'
                        ]
                    ]
                ]);
                $event->sheet->getDelegate()->getStyle('A3:C3')->applyFromArray([
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                        'wrapText' => true,
                    ],
                    'font' => [
                        'name' => 'Arial',
                        'bold' => true,
                        'italic' => false,
                        'strikethrough' => false,
                        'color' => [
                            'rgb' => '4472C4'
                        ]
                    ],
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => Border::BORDER_THIN,
                            'color' => [
                                'rgb' => '808080'
                            ]
                        ]
                    ],
                    'fill' => [
                        'fillType' => 'linear', //线性填充，类似渐变
                        'rotation' => 45, //渐变角度
                        'startColor' => [
                            'rgb' => 'F2F2F2' //初始颜色
                        ],
                        //结束颜色，如果需要单一背景色，请和初始颜色保持一致
                        'endColor' => [
                            'argb' => 'F2F2F2'
                        ]
                    ],
                ]);
                $event->sheet->getDelegate()->getStyle('A4:C'.(3+$this->count))->applyFromArray([
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                        'wrapText' => true,
                    ],
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => Border::BORDER_THIN,
                            'color' => [
                                'rgb' => '808080'
                            ]
                        ]
                    ],
                ]);

                //合并单元格
                $event->sheet->getDelegate()->mergeCells('A1:C1');
                $event->sheet->getDelegate()->mergeCells('A2:C2');
            }
        ];
    }
}
